package com.oha.laporrayelchurro.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.apache.log4j.Logger;

import com.oha.laporrayelchurro.model.Pronostico;
import com.oha.laporrayelchurro.service.H2DataBaseService;

public class PronosticoDao extends H2BaseDao{

	private static Logger log4j = Logger.getLogger(PronosticoDao.class);

	public void save(Pronostico model){
		String sql ="insert into pronostico(idUsuario, idPartido, goles1, goles2, pais1, pais2) values(?,?,?,?,?, ?)";
		Connection conn = null;
		PreparedStatement pstm = null;
		try {
			H2DataBaseService dbs = new H2DataBaseService();
			conn =dbs.getConnection();
			pstm = conn.prepareStatement(sql);
			int index=1;
			pstm.setInt(index++, model.getIdUsuario());
			pstm.setInt(index++, model.getIdPartido());
			pstm.setInt(index++, model.getGoles1());
			pstm.setInt(index++, model.getGoles2());
			if(model.getPais1()!=null && model.getPais1().trim().length()>0){
				pstm.setString(index++, model.getPais1());
			}else{
				pstm.setNull(index++, Types.VARCHAR);
			}
			if(model.getPais2()!=null && model.getPais2().trim().length()>0){
				pstm.setString(index++, model.getPais2());
			}else{
				pstm.setNull(index++, Types.VARCHAR);
			}
			pstm.executeUpdate();
		} catch (SQLException e) {
			log4j.error("SQLException.save.getP.", e);
		}finally{
			close(pstm);
			close(conn);
		}
	}
	
	/**
	 * Datos
	 * @return
	 */
	public String strData(int idUsuario, int tipo){
		StringBuilder data = new StringBuilder("\"");
		SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm");
		String sql = "select par.idPartido, par.hora, coalesce(pro.pais1, par.pais1), pro.goles1, coalesce(pro.pais2, par.pais2), pro.goles2".concat(
		             "  from partido par inner join pronostico pro on pro.idPartido=par.idPartido").concat(
		             " where pro.idUsuario=?").concat(
		             "   and par.tipo=?").concat(
		             "  order by par.hora asc");
		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			H2DataBaseService dbs = new H2DataBaseService();
			conn =dbs.getConnection();
			pstm = conn.prepareStatement(sql);
			int index=1;
			pstm.setInt(index++, idUsuario);
			pstm.setInt(index++, tipo);
			rs = pstm.executeQuery();
			int nItem = 0;
			int iniLine=0;
			while(rs.next()){
				if(data.length()>1){
					data.append("#");
					if(nItem>=8){
						nItem=0;
						data.append(iniLine!=0 ? "\")":"\"");
						data.append(".concat(");
						data.append("\n\"");
						iniLine++;
					}
				}
				data.append(rs.getInt(1));//id
				data.append(";").append(sdf.format(new Date(rs.getTimestamp(2).getTime())));//fecha
				data.append(";").append(rs.getString(3));//pais1
				data.append(";").append(rs.getInt(4));//goles1
				data.append(";").append(rs.getString(5));//pais2
				data.append(";").append(rs.getInt(6));//goles2
				nItem++;
			}
			data.append(tipo!=1 ? "#":"");
			data.append("\")");
		} catch (SQLException e) {
			log4j.error("SQLException.save.getP.", e);
		}finally{
			close(rs);
			close(pstm);
			close(conn);
		}
		return data.toString();
	}
	
	/**
	 * Elimina el pronostico del usuario indicado
	 * @param idUsuario
	 */
	public void delete(int idUsuario){
		String sql ="delete from pronostico where idUsuario=?";
		Connection conn = null;
		PreparedStatement pstmDel = null;
		try {
			H2DataBaseService dbs = new H2DataBaseService();
			conn =dbs.getConnection();
			pstmDel = conn.prepareStatement(sql);
			pstmDel.setInt(1, idUsuario);
			pstmDel.executeUpdate();
		} catch (SQLException e) {
			log4j.error("SQLException.save.getP.", e);
		}finally{
			close(pstmDel);
			close(conn);
		}
	}
	
}
